﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LanguageInfo.IDAL;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using LanguageInfo.Data.Helper;

namespace LanguageInfo.DAL
{
    public class CorporaDAL : DataBaseClass, ICorporaDAL
    {

        public DataSet GetCorporaInfo(string strInfo, int StartIndex, int EndIndex)
        {
            string sbSql = BuildSqlCorporaInfo(strInfo);
            sbSql = "select * from(" + sbSql + ") TBINFO where rowid  BETWEEN {0} AND {1}  order by CreateTime desc ; ";
            sbSql = string.Format(sbSql, StartIndex, EndIndex);

            StringBuilder strCountSql = new StringBuilder();
            strCountSql.Append("SELECT COUNT(*) as PageCount FROM (");
            strCountSql.Append(BuildSqlCorporaInfo(strInfo));
            strCountSql.Append(") TBCOUNT");
            sbSql += strCountSql.ToString();

            using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
            {
                if (!string.IsNullOrEmpty(strInfo))
                {
                    AddInParameter(cmd, "@SentenceEn", strInfo);
                }
                //MatchCollection mc;
                //mc = Regex.Matches(cmd.CommandText, @"(@[^ \(]+?)(\,| |$|\))");
                //int count = mc.Count / 2;
                //for (int i = 0; i < count; i++)
                //{
                //    AddInParameter(cmd, mc[i].Groups[1].Value, objArryParam[i]);
                //}
                return db.ExecuteDataSet(cmd);
            }
        }
        public DataSet GetCorporaInfoById(string strId)
        {
            if (!string.IsNullOrEmpty(strId))
            {
                string sbSql = "select * from LI_RESOLVEISENTENCE where SENTENCEID=@SENTENCEID";

                using (DbCommand cmd = db.GetSqlStringCommand(sbSql))
                {
                    AddInParameter(cmd, "@SENTENCEID", strId);
                    return db.ExecuteDataSet(cmd);
                }
            }
            return null;
        }
        public DataSet GetCorporaInfoByUserName()
        {
            string sbSql = "select Translator as StatisType,count(SentenceId) as StatisTypeNum,SUM(len(SentenceEn)) as StatisSize  from LI_RESOLVEISENTENCE group by Translator";

            using (DbCommand cmd = db.GetSqlStringCommand(sbSql))
            {
                return db.ExecuteDataSet(cmd);
            }
        }
        private string BuildSqlCorporaInfo(string strInfo)
        {
            string strSql = @"select *,ROW_NUMBER() OVER(ORDER BY [CREATETIME] DESC) AS RowId from LI_RESOLVEISENTENCE ";
            if (!string.IsNullOrEmpty(strInfo))
            {
                strSql += " where SentenceEn like '%'+ @SentenceEn + '%'";
            }
            return strSql;
        }

        public int DelCorpora(string _SentenceId)
        {
            string cmdText = @"delete from [LI_RESOLVEISENTENCE] WHERE [SENTENCEID] = @SENTENCEID";
            SqlParameter[] parameters = new SqlParameter[]
                {
                    new SqlParameter("@SENTENCEID",SqlDbType.VarChar)
                };
            parameters[0].Value = _SentenceId;
            int excInt = DbHelperSQL.ExecuteSql(cmdText, parameters);
            return excInt;
        }

        /// <summary>
        /// 批量新增句子
        /// </summary>
        /// <param name="AddTableds"></param>
        public void InsertResolveiSentenceInfo(DataSet AddTableds)
        {
            //连接串
            //string connectionString = OracleDbHelper.OracleConnString;
            string cmdText_Table = @"INSERT INTO LI_RESOLVEISENTENCE(SENTENCEID,PASSAGESID,SENTENCEEN,SENTENCECH,ORIGIN,WRITER,TRANSLATOR,USERNAME,CREATETIME) VALUES("
                                + "@SENTENCEID,@PASSAGESID,@SENTENCEEN,@SENTENCECH,@ORIGIN,@WRITER,@TRANSLATOR,@USERNAME,@CREATETIME)";
            AddTableds.Tables[0].TableName = "LI_RESOLVEISENTENCE";
            DbCommand cmd = db.GetSqlStringCommand(cmdText_Table);
            db.AddInParameter(cmd, "SENTENCEID", DbType.String, "SENTENCEID", DataRowVersion.Current);
            db.AddInParameter(cmd, "PASSAGESID", DbType.String, "PASSAGESID", DataRowVersion.Current);
            db.AddInParameter(cmd, "SENTENCEEN", DbType.String, "SENTENCEEN", DataRowVersion.Current);
            db.AddInParameter(cmd, "SENTENCECH", DbType.String, "SENTENCECH", DataRowVersion.Current);
            db.AddInParameter(cmd, "ORIGIN", DbType.String, "ORIGIN", DataRowVersion.Current);
            db.AddInParameter(cmd, "WRITER", DbType.String, "WRITER", DataRowVersion.Current);
            db.AddInParameter(cmd, "TRANSLATOR", DbType.String, "TRANSLATOR", DataRowVersion.Current);
            db.AddInParameter(cmd, "USERNAME", DbType.String, "USERNAME", DataRowVersion.Current);
            db.AddInParameter(cmd, "CREATETIME", DbType.DateTime, "CREATETIME", DataRowVersion.Current);
            db.UpdateDataSet(AddTableds, AddTableds.Tables[0].TableName, cmd, null, null, UpdateBehavior.Transactional);
        }

        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="UpdateByIdOrOrderDs">更新的DataSet</param>
        public void UpdateResolveiSentenceInfo(DataSet UpdateByIdOrOrderDs)
        {

            string cmdText_Table = @"update LI_RESOLVEISENTENCE set PASSAGESID=@PASSAGESID,SENTENCEEN=@SENTENCEEN,
                        SENTENCECH=@SENTENCECH,ORIGIN=@ORIGIN,WRITER=@WRITER,TRANSLATOR=@TRANSLATOR,USERNAME=@USERNAME 
                    where SENTENCEID=@SENTENCEID";
            UpdateByIdOrOrderDs.Tables[0].TableName = "LI_RESOLVEISENTENCE";
            DbCommand cmd = db.GetSqlStringCommand(cmdText_Table);
            db.AddInParameter(cmd, "SENTENCEID", DbType.String, "SENTENCEID", DataRowVersion.Current);
            db.AddInParameter(cmd, "PASSAGESID", DbType.String, "PASSAGESID", DataRowVersion.Current);
            db.AddInParameter(cmd, "SENTENCEEN", DbType.String, "SENTENCEEN", DataRowVersion.Current);
            db.AddInParameter(cmd, "SENTENCECH", DbType.String, "SENTENCECH", DataRowVersion.Current);
            db.AddInParameter(cmd, "ORIGIN", DbType.String, "ORIGIN", DataRowVersion.Current);
            db.AddInParameter(cmd, "WRITER", DbType.String, "WRITER", DataRowVersion.Current);
            db.AddInParameter(cmd, "TRANSLATOR", DbType.String, "TRANSLATOR", DataRowVersion.Current);
            db.AddInParameter(cmd, "USERNAME", DbType.String, "USERNAME", DataRowVersion.Current);
            db.UpdateDataSet(UpdateByIdOrOrderDs, UpdateByIdOrOrderDs.Tables[0].TableName, cmd, null, null, UpdateBehavior.Transactional);
        }
        ///// <summary>
        ///// 批量删除
        ///// </summary>
        ///// <param name="UpdateByIdOrOrderDs">更新的DataSet</param>
        //public void DelRefFieldInfo(DataSet DelDs)
        //{
        //    //连接串
        //    string connectionString = OracleDbHelper.OracleConnString;

        //    string cmdText_Table = @"DELETE FROM TBL_META_REFERENCEFIELD WHERE ID=:ID";
        //    DelDs.Tables[0].TableName = "TBL_META_REFERENCEFIELD";
        //    DbCommand cmd = db.GetSqlStringCommand(cmdText_Table);
        //    db.AddInParameter(cmd, "ID", DbType.String, "ID", DataRowVersion.Current);
        //    db.UpdateDataSet(DelDs, DelDs.Tables[0].TableName, cmd, null, null, Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Transactional);
        //}

    }
}
